﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using EB.Generics;
using System.Data.SqlClient;
using System.Data.Sql;
/// <summary>
/// Summary description for Inbox
/// </summary>
namespace EB.Inbox
{
    #region  Struct
    public struct InboxSearchCriteria
    {
        string _setCriteria;

        public string SetCriteria
        {
            get { return _setCriteria; }
            set { _setCriteria = value; }
        }

        string _firstName;

        public string FirstName
        {
            get { return _firstName; }
            set { _firstName = value; }
        }

        int _requestType;

        public int RequestType
        {
            get { return _requestType; }
            set { _requestType = value; }
        }

        string _dateofCreation;

        public string DateofCreation
        {
            get { return _dateofCreation; }
            set { _dateofCreation = value; }
        }
        string _dateofCreation1;

        public string DateofCreation1
        {
            get { return _dateofCreation1; }
            set { _dateofCreation1 = value; }
        }
        string _status;

        public string DateStatus
        {
            get { return _status; }
            set { _status = value; }
        }

        int _reqStatus;
        public int Status
        {
            get { return _reqStatus; }
            set { _reqStatus = value; }
        }
    }
    #endregion


    public class Inbox
    {
        #region Fields
        EB.Generics.Generics commonClass = new EB.Generics.Generics();
        #endregion

        #region Constructor
        public Inbox()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        #endregion

        #region Methods

        /// <summary>
        /// Insert data into Inbox
        /// </summary>
        /// <param name="RequestTypeID"></param>
        /// <param name="actorID"></param>
        /// <param name="linkKey"></param>
        /// <returns></returns>
        public int CreateInBox(int RequestTypeID, int actorID, int linkKey)
        {
            #region Fields
            int uniqId=0;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            if (actorID == 0)
            {

                query = "INSERT INTO dbo.InBox(RequestType, RequestLink, UserID) " +
                        " VALUES     ('" + RequestTypeID + "','" + linkKey + "',NULL) Select Scope_Identity()";
            }
            else
            {
                query = "INSERT INTO dbo.InBox(RequestType, RequestLink, UserID) " +
                        " VALUES     ('" + RequestTypeID + "','" + linkKey + "','" + actorID + "') Select Scope_Identity()";
            }
            object id=commonClass.ExecuteScalar(query);
            uniqId = Convert.ToInt16(id);
            return uniqId;
        }

        /// <summary>
        /// Update the Inbox Status
        /// </summary>
        /// <param name="uniqId"></param>
        /// <param name="requester"></param>
        /// <param name="status"></param>
        /// <param name="note"></param>
        /// <returns></returns>
        public bool UpdateInBoxStatus(int uniqId,int requester,int status,string note)
        {
            #region Fields
            bool status1;
            object identity = 0;
            string query = string.Empty;
            #endregion
            query = "UPDATE    InBox SET  Requester ='" + requester + "', Status ='" + status + "', Note ='"+note+"' where RecordNumber='"+uniqId+"'";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status1 = true;
            else
                status1= false;
            return status1;                   

        }
      
        /// <summary>
        /// To view the Inbox Entries
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataSet GetInboxEntries(int userId)
        {
            #region Fields
            string query = string.Empty;
            DataSet inboxDS;
            #endregion

            inboxDS = new DataSet();
            query = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') "+
                      " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, "+
                      " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, "+
                      " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status "+
                      " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN "+
                      " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN "+
                      " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "') and InBox.Status!=4  order by  InboxStatusList.Status ";
            inboxDS = commonClass.ExecuteQuery(query);
            return inboxDS;
            //1. Query in InboxView with user ID stored in session[LogedInUser]
            //2. Use background colour in showing the entries in table. Rendering to be done in the page only.
            //3. Icon to be shown as the frst item in the table column
        }

        public DataSet GetAllInboxEntries(int userId)
        {
            #region Fields
            string query = string.Empty;
            DataSet inboxDS;
            #endregion

            inboxDS = new DataSet();
            query = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                      " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                      " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                      " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                      " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                      " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                      " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "')  order by  InboxStatusList.Status ";
            inboxDS = commonClass.ExecuteQuery(query);
            return inboxDS;
            //1. Query in InboxView with user ID stored in session[LogedInUser]
            //2. Use background colour in showing the entries in table. Rendering to be done in the page only.
            //3. Icon to be shown as the frst item in the table column
        }
        /// <summary>
        /// Get the usercontrol name from database
        /// </summary>
        /// <param name="RequestTypeID"></param>
        /// <param name="recordNumber"></param>
        /// <returns></returns>
        public string GetPathNameByRequestTypeAndUserId(int RequestTypeID,int recordNumber)
        {
             #region Fields
            string query = string.Empty;
            string controlPath = string.Empty;
            #endregion
            query="SELECT     ModuleControlCollection.ControlPath FROM         ModuleControlCollection INNER JOIN "+
                  " RequestTypeList ON ModuleControlCollection.RequestTypeID = RequestTypeList.RequestTypeID INNER JOIN "+
                      " InBox ON RequestTypeList.RequestTypeID = InBox.RequestType WHERE InBox.RequestType='"+RequestTypeID+"' and InBox.RecordNumber='"+recordNumber+"'";
            DataSet controlDS = commonClass.ExecuteQuery(query);
            if (controlDS.Tables[0].Rows.Count > 0)
            {
                controlPath = controlDS.Tables[0].Rows[0]["ControlPath"].ToString();
            }
            return controlPath;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="recordNumber"></param>
        /// <param name="viewDate"></param>
        /// <param name="processedDate"></param>
        /// <param name="status"></param>
        /// <param name="note"></param>
        /// <returns></returns>
        public bool UpdateInboxStatusByRecordNumber(int recordNumber,string viewDate,string processedDate,int status,string note)
        {
            #region Fields
            bool status1;
            object identity = 0;
            string query = string.Empty;
            #endregion
            query = "UPDATE    InBox SET  DateViewed ='" + viewDate + "', DateProcessed ='" + processedDate + "', Status ='" + status + "', Note ='" + note + "' where RecordNumber='" + recordNumber + "'";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status1 = true;
            else
                status1 = false;
            return status1;        
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="status"></param>
        /// <param name="requestTypeID"></param>
        /// <param name="linkKey"></param>
        /// <returns></returns>
        public bool UpdateRequestLinkStatus(int status,int requestTypeID,int linkKey,int stepStatus)
        {
             #region Fields
            bool status1;
            object identity = 0;
            string query = string.Empty;
            #endregion
            query = "UPDATE    RequestList SET  Status ='" + status + "',StepStatus='"+stepStatus+"' where RequestTypeID ='" + requestTypeID + "' and LinkKey ='" + linkKey + "'";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status1 = true;
            else
                status1 = false;
            return status1;
        }

        public bool DeleteInBoxEntries(int recordNo)
        {
            #region Fields
            bool status;
            string query = string.Empty;
            #endregion
            query = "DELETE FROM InBox where RecordNumber='"+recordNo+"'";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        public DataSet GetRequestType()
        {
            #region Fields
            string query = string.Empty;
            DataSet requestTypeDS;
            #endregion

            requestTypeDS = new DataSet();
            query = "SELECT     RequestTypeID, RequestName FROM         RequestTypeList";
            requestTypeDS = commonClass.ExecuteQuery(query);
            return requestTypeDS;

        }
        public DataSet GetInboxStatusList()
        {
            #region Fields
            string query = string.Empty;
            DataSet inboxStatusDS;
            #endregion

            inboxStatusDS = new DataSet();
            query = "SELECT     Status, StatusNote  FROM         InboxStatusList";
            inboxStatusDS = commonClass.ExecuteQuery(query);
            return inboxStatusDS;
        }
        public bool SearchInbox(InboxSearchCriteria criteria,int userId, out DataSet result)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            SqlDataAdapter sqlDataAdapter = commonClass.SQLDataAdapter();
            result = new DataSet();

            if (criteria.FirstName != null && criteria.FirstName!="")
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                       " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                       " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                       " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                       " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                       " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                       " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "')  and  (UserDetails.UD_FirstName " + "like '" + '%' + "' + '" + criteria.FirstName + "' + '" + '%' + "' or UserDetails.UD_MiddleName " + "like '" + '%' + "' + '" + criteria.FirstName + "' + '" + '%' + "' or UserDetails.UD_LastName " + "like '" + '%' + "' + '" + criteria.FirstName + "' + '" + '%' + "')";




            }
            else if (criteria.Status!=0)
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                       " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                       " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                       " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                       " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                       " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                       " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "') and InBox.Status='"+ criteria.Status +"' ";

            }
            else if (criteria.RequestType != 0)
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                       " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                       " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                       " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                       " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                       " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                       " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "') and InBox.RequestType='" + criteria.RequestType + "' ";

            }
            else if (criteria.DateofCreation!= null &&  criteria.DateofCreation!="")
            {
                if (criteria.DateStatus == "Between")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                       " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                       " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                       " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                       " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                       " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                       " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "') "+
                       " and convert(varchar,InBox.DateOfCreation,103) between convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) and convert(varchar,cast('" + criteria.DateofCreation1 + "' as datetime),103)";

                }
                else if (criteria.DateStatus == "<=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                       " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                       " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                       " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                       " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                       " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                       " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "') " +
                       " and convert(varchar,InBox.DateOfCreation,103) <= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";

                }
                else if (criteria.DateStatus == ">=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                           " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                           " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                           " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                           " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                           " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                           " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "') " +
                           " and convert(varchar,InBox.DateOfCreation,103) >= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";

                }
                else
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                           " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                           " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                           " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                           " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                           " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                           " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "') " +
                           " and convert(varchar,InBox.DateOfCreation,103) = convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";

                }
            }
            else
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     InBox.Note, CONVERT(varchar, InBox.DateOfCreation, 103) AS DateOfRequest, ISNULL(UserDetails.UD_FirstName, '') " +
                       " + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, RequestTypeList.RequestName, " +
                       " InBox.RequestType, InBox.RequestLink, InBox.DateOfCreation, InBox.DateViewed, InBox.DateProcessed, InBox.RecordNumber, " +
                       " InboxStatusList.StatusNote, UserDetails_1.UD_UserPersonalID, UserDetails.UD_UserPersonalID AS Expr1,InboxStatusList.Status " +
                       " FROM         InBox INNER JOIN   RequestTypeList ON InBox.RequestType = RequestTypeList.RequestTypeID INNER JOIN " +
                       " InboxStatusList ON InBox.Status = InboxStatusList.Status INNER JOIN  UserDetails AS UserDetails_1 ON InBox.UserID = UserDetails_1.UD_UserPersonalID LEFT OUTER JOIN " +
                       " UserDetails ON InBox.Requester = UserDetails.UD_UserPersonalID WHERE     (InBox.UserID = '" + userId + "') ";


            }
            sqlDataAdapter.Fill(result);
            return true;
        }

        #endregion

    }
 
}